﻿using System;
using System.Net;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Data.Models;
using System.Collections.Generic;
using AccessControl.Contracts.Models;

namespace AccessControl.DataManagers.Tables.Managers
{
	/// <summary>
	/// Manages the Cameras in the DB
	/// </summary>
	class CamerasManager : TableManager<ICamera>
	{
		#region Fields

		private List<ICamera> _cameras;
		private SqlDataAdapter _adapter;
		private static CamerasManager s_Instance = null;

		private static readonly string COL_1_ID = "ID";
		private static readonly string COL_2_IP = "IP";
		private static readonly string COL_3_PORT_NUMBER = "PORT_NUMBER";
		private static readonly string COL_4_RELATED_ENTRY = "RELATED_ENTRY_ID";

		#endregion

		#region Private Constructor

		/// <summary>
		/// Prevents a default instance of the <see cref="CamerasManager"/> class from being created.
		/// </summary>
		private CamerasManager()
		{
		}

		#endregion

		#region Public Properties

		/// <summary>
		/// Gets the instance.
		/// </summary>
		public static CamerasManager Instance
		{
			get
			{
				lock (typeof(CamerasManager))
				{
					if (s_Instance == null)
						s_Instance = new CamerasManager();

					return s_Instance;
				}
			}
		}

		#endregion

		#region TableManager Overrides

		/// <summary>
		/// Creates using the specified connection.
		/// </summary>
		/// <param name="connection">The connection.</param>
		public override void Create(SqlConnection connection)
		{
			SqlCommand command = connection.CreateCommand();
			command.CommandText = string.Format("CREATE TABLE {0} ({1} INT, {2} TEXT, {3} INT, {4} INT)",
												TablesNames.CAMERAS, COL_1_ID, COL_2_IP, COL_3_PORT_NUMBER, COL_4_RELATED_ENTRY);
			command.ExecuteNonQuery();            
		}

		/// <summary>
		/// Initializes this instance.
		/// </summary>
		public override void Initialize(SqlConnection connection)
		{
			/* Create the adapter */
			_adapter = new SqlDataAdapter();
			/* Create the adapter commands */
			_adapter.SelectCommand = new SqlCommand(string.Format("SELECT * FROM {0}", TablesNames.CAMERAS), connection);

			_adapter.InsertCommand = new SqlCommand((string.Format("INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES (@{1}, @{2}, @{3}, @{4})",
													 TablesNames.CAMERAS, COL_1_ID, COL_2_IP, COL_3_PORT_NUMBER, COL_4_RELATED_ENTRY)), connection);

			_adapter.UpdateCommand = new SqlCommand((string.Format("UPDATE {0} SET {2} = @{2}, {3} = @{3}, {4} = @{4} WHERE {1} = @{1}",
													 TablesNames.CAMERAS, COL_1_ID, COL_2_IP, COL_3_PORT_NUMBER, COL_4_RELATED_ENTRY)), connection);

			_adapter.DeleteCommand = new SqlCommand(string.Format("DELETE FROM {0} WHERE {1} = @{1}",
													TablesNames.CAMERAS, COL_1_ID), connection);
		}

		/// <summary>
		/// Gets the list.
		/// </summary>
		/// <returns></returns>
		public override bool FillList(IList<ICamera> items)
		{
			Camera camera;

			using (SqlDataReader reader = _adapter.SelectCommand.ExecuteReader())
			{
				while (reader.Read())
				{
					camera = new Camera()
					{
						Id = reader.GetInt32(0),
						Ip = IPAddress.Parse(reader.GetString(1)),
						PortNumber = reader.GetInt32(2),
						RelatedEntryId = reader.GetInt32(3)
					};

					items.Add(camera);
				}
			}
			
			return true;
		}

		/// <summary>
		/// Adds the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Add(ICamera item)
		{
			_adapter.InsertCommand.Parameters.Clear();

			/* Set the item id */
			item.Id = GetHighestId(_adapter.SelectCommand.Connection, TablesNames.CAMERAS, COL_1_ID) + 1;

			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_2_IP, item.Ip.ToString()));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_3_PORT_NUMBER, item.PortNumber));
			_adapter.InsertCommand.Parameters.Add(new SqlParameter(COL_4_RELATED_ENTRY, item.RelatedEntryId));

			return (_adapter.InsertCommand.ExecuteNonQuery() > 0);
		}

		/// <summary>
		/// Updates the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Update(ICamera item)
		{
			_adapter.UpdateCommand.Parameters.Clear();

			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_2_IP, item.Ip.ToString()));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_3_PORT_NUMBER, item.PortNumber));
			_adapter.UpdateCommand.Parameters.Add(new SqlParameter(COL_4_RELATED_ENTRY, item.RelatedEntryId));

			return (_adapter.UpdateCommand.ExecuteNonQuery() > 0);
		}

		/// <summary>
		/// Deletes the specified item.
		/// </summary>
		/// <param name="item">The item.</param>
		public override bool Delete(ICamera item)
		{
			_adapter.DeleteCommand.Parameters.Clear();

			_adapter.DeleteCommand.Parameters.Add(new SqlParameter(COL_1_ID, item.Id));

			return (_adapter.DeleteCommand.ExecuteNonQuery() > 0);

		}

		#endregion
	}
}
